/********************************************************************
Renaming the Serrano file and mergint the UC and the Serrano database: basic firm data
**********************************************************************/

/*Preparing the Serrano file for matching with UC*/
clear all

use "serrano_20190216.dta" /*Load Serrano data*/

*We drop variables that are not relevant for the current project and rename some variables to match the database with UC. In some cases, we copy a firm characteristics and rename a variable so that the name is self-explanatory*/
drop rectyp-peorgnr_lopnnr

rename tillgsu ta   /*total assets*/

rename redtyp redovtyp /*accounting type*/

drop bol_kkfall-bol_q37dat /*Firm characteristics such as information on liquidation and bankruptcy*/

drop utdbel   /*dividend*/

rename bslslut bksdatt  /*firm year end*/
rename bslstart bksdatf 	/*firm year start*/

		 
		 *use all assets
		 
		 
		 **************************ASSETS

replace material_assets=0 if material_assets==.
label var material_assets "ppe"


egen receivable_customers = rowtotal(kundford ) /*Kundford: current receivables from the company's customers*/
drop kundford
replace receivable_customers=0 if receivable_customers==.

egen cash_sec= rowtotal(kplacsu )  /*kplacsu: investments in securities*/
replace cash_sec = 0 if cash_sec==.
drop kplacsu

/*g ta=tillgsu*/
label var ta "total_assets"
recast double ta

*egen ta = rowtotal(cash_sec total_fixed_assets inventory receivable_customers)

g total_current_assets=omstgsu
drop omstgsu


**************Debt!!

													g acc_pay=ksklev    /*account payable*/
													drop ksklev



													gen xternal_long_debt=lsksu
													drop lsksu

													replace xternal_long_debt=0 if xternal_long_debt==.

													*short term debt


													

													gen xternal_short_debt = ksksu
													drop ksksu
													**accounts payable********************
													label var acc_pay "accounts payable, lev kred"



													egen net_working_capital=rowtotal(total_current_assets cash_sec acc_pay -xternal_short_debt)


													***************
													*total debt
													egen total_debt = rowtotal(xternal_short_debt xternal_long_debt)

g or_r_leverage = total_debt/ta





********************************************


	g interest=rtekoext  /*external interest expenses*/
	
	
	egen net_financial_res = rowtotal(rteinext -rtekoext) /*rteinext: external interest income, net_financial_res is the difference between external interest income and costs*/
label var net_financial_res "fin income minus fin costs"
	
rename anltsu matanlsu /*anltsu: total fixed assets*/
	
	 
	 g ppe=matanlsu   /*ppe: property, plant and equipment; matanlsu: tangible fixed assets*/
	 
	 
	 label var ppe "prop plant eq"
	 

	
	 
	 
	 
	 g or_lcash=log(cash_sec)
	 
	 

rename avskriv depreciation_all  /*avskriv stands for avskrivningar, which is depreciation in Swedish*/

		label var depreciation_all "depreciations"
		sort bidnr year, stable

		g investment_net=(ppe-l.ppe)+depreciation_all
		label var investment_net "good capx"

		
		

			g cash_flow=rorresul+depreciation_all /*rorresul (rorelseresultat in Swedish): EBIT*/

		 g investment_gross=(ppe-l.ppe)
		 label var investment_gross "change in ppe ign depr"
		 
		gen ebit =rorresul	
		drop rorresul
		
		 
rename new_bransch rbsni  /*NACE codes*/
		g return_net= resefin /*Profit/Loss after net financial income/expenses*/
		
/*Revising format: the format is string in the UC, and it is long in serrano (displayed as series of numbers). I divided the date into 3 parts, and constructed the corresponding string date that is similar to the UC in terms of format*/
format bksdatf bksdatt %td   /*bksdatf variable is bokforingsdatum or accounting date*/
	g bksdatf_year=year(bksdatf)
	g bksdatf_month=month(bksdatf)
	g bksdatf_day=day(bksdatf)
	tostring bksdatf_day, replace
	tostring bksdatf_month, replace
	tostring bksdatf_year, replace
	
g bksdatf_str=bksdatf_year +"-"+bksdatf_month+"-"+bksdatf_day  
drop bksdatf
rename bksdatf_str bksdatf

	g bksdatt_year=year(bksdatt)
	g bksdatt_month=month(bksdatt)
	g bksdatt_day=day(bksdatt)
	tostring bksdatt_day, replace
	tostring bksdatt_month, replace
	tostring bksdatt_year, replace
	
g bksdatt_str=bksdatt_year +"-"+bksdatt_month+"-"+bksdatt_day  
drop bksdatt
rename bksdatt_str bksdatt

drop bksdatf_day bksdatf_month bksdatf_year bksdatt_day bksdatt_month bksdatt_year  /*I delete the supplementary, assistant variables*/


/*Match SNI 2007 files with SNI92 to achieve consistent industry classification

cd "\\micro.intra\Projekt\P0789$\P0789_Gem\Laszlo\data_serrano"


rename bransch_sni071_konv SNI_2007_1
tostring SNI_2007_1, replace

merge m:1 SNI_2007_1 using conversion_92_2007.dta    *conversion_92_2007 is derived from correspondence_92_2007 (code for this is located in 2-industry classification), 
													 *we dropped duplicates in terms of SNi2007 codes*


drop _merge

rename SNI_92_1 SNI92

replace SNI92=substr(SNI92,1,2)
*/

sort bidnr year, stable

quietly by bidnr year: gen dup=cond(_N==1,0,_n)

drop if dup>1
drop dup

*The code so far produces the Serrano file we use for matching with UC* 
save serrano_renamed_20190216.dta, replace

/*Merging UC and Serrano*/
clear all

use "uc_2019_renamed.dta" /*UC data*/

merge 1:1 bidnr year using serrano_renamed_20190216.dta,force

duplicates tag bidnr year,generate(dup)

table dup  /*No duplicates*/

drop dup _merge

save merge_UC_Serrano_20190218.dta, replace   /*This yields the merged UC and Serrano file that will be extended with firm characteristics below*/

		
*Merging the matched UC-Serrano file with firm characteristics*/
clear all

use "merge_UC_Serrano_20190218.dta", clear

*removing variables that are unnecessary, but keep ftg kategori and jurform for later purposes


*Information on fiscal years
gen bokslut_start=date( bksdatf, "YMD")
gen bokslut_end=date( bksdatt , "YMD")
g days= bokslut_end- bokslut_start
/*drop month_s*/
g start_month=month( bokslut_start)

*labels and dropping

drop full_yr_jan
label var interest "gross fin costs"
replace interest=interest*-1 if year>1996 /*negative values need to be fixed*/
label var interest "gross fin costs rtekoext"
drop rtekoext
replace depreciation_all = depreciation_all *-1 if year>1996 /*negative values need to be fixed*/ 
replace skatter = skatter *-1 if year>1996 /*negative values need to be fixed*/ /*skatter: taxes*/
replace perskos = perskos *-1 if year>1996 /*negative values need to be fixed*/  /*Personal costs*/
replace ebitda = ebit+ depreciation_all if year>1996 /*negative values need to be fixed*/
label var eksu "equity"
drop cash_flow
g cash_flow=ebit+depreciation_all


drop reseftav
drop resfbok
drop net_financial_res
drop rbakap ovomstil
drop matanlsu
drop or*
duplicates tag bidnr year, g(tag)
sum tag
drop ser_year

*drop tag

*In the final section of the code, we finalize the datafile by cleaning the variable on number of employees and merging it with the emission data. The latter step is necessary to  make sure we will not miss firms with available fossil emissions. */

*Cleaning number of employees variable*/
merge 1:1 bidnr year using "basic_antanst_etc.dta"  /*This file (number of employees) is produced based on the aggregation of the installation-level data to firm level*/
replace antanst = ser_antanst if year>1996 /*We replace the number of employees with the one in Serrano after 1996 (when Serrano has a good coverage)*/
drop ser_antanst
/*drop _merge*/

save  "merge_UC_Serrano_20190218_trans.dta" , replace  /*We save the merged Serrano-UC-#employee data*/
use "merge_UC_Serrano_20190218_trans.dta" , clear

drop if _merge==2
drop _merge

merge 1:1 bidnr year using   "emissions_basic.dta"  /*Match firm-level fossil CO2 emissions*/

 
drop _merge ar kt_co2foss pr_co2_kt
save  "merge_UC_Serrano_firm_characteristics.dta", replace
